﻿Imports System.Data
Imports System.Data.Odbc
Module dataEditaDepartamento
    Public datoDepatamento As Integer
    Public idDepCargo As Integer
    Public idDepDueño As Integer
    Public idGrupoDpto As Integer
    Public idBlockDpto As Integer

    Sub completarDatosDpto()
        conectar()


        cmd.Connection = con
        cmd.CommandText = "SELECT d.ID,d.NumeroActual,d.NumeroAnterior, b.ID,b.Nombre,g.ID, g.Nombre,d.IDPersona, d.ROL, d.NIC, d.NICAgua ,d.IDPropietario "
        cmd.CommandText += "FROM tgruposdegastos g ,tBlock b, tDepartamento d INNER JOIN tPersonas p ON p.ID=d.IDPropietario "
        cmd.CommandText += "WHERE  d.ID=" & datoDepatamento
        dr = cmd.ExecuteReader

        If dr.Read Then
            With frmEditaDepartamento
                .txtActual.Text = dr.Item(1).ToString
                .txtAnterior.Text = dr.Item(2).ToString
                idBlockDpto = dr.Item(3)
                .txtBlock.Text = dr.Item(4).ToString
                idGrupoDpto = dr.Item(5)
                .txtGrupoGastos.Text = dr.Item(6).ToString
                'idDepCargo = dr.Item(7)
                idDepCargo = dr.Item(11)
                .txtRol.Text = dr.Item(8).ToString
                .txtNic.Text = dr.Item(9).ToString
                .txtNicAgua.Text = dr.Item(10).ToString
                idDepDueño = dr.Item(11)


            End With

        End If
        dr.Close()
        desconectar()
    End Sub

    Function datosEditarCBPersona()
        Dim contador As Integer
        Dim val As Integer = 0


        Dim i As Integer

        frmEditaDepartamento.cbPersona.Items.Clear()
        conectar()
        cmd.Connection = con
        cmd.CommandText = "SELECT count(Nombre) FROM tpersonas"
        cmd.CommandType = CommandType.Text
        dr = cmd.ExecuteReader
        If dr.Read Then
            contador = dr.Item(0)
        End If
        dr.Close()

        Dim VectorDepartamento(contador, 1) As String
        cmd.Connection = con
        cmd.CommandText = "SELECT ID, Nombre, ApPaterno, ApMaterno FROM  tpersonas ORDER BY Nombre"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString

                VectorDepartamento(i, 1) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString

                frmEditaDepartamento.cbPersona.Items.Add(VectorDepartamento(i, 1))
                i += 1

            End While
        End If
        dr.Close()
        desconectar()

        Return VectorDepartamento



    End Function

    Function datosEditarCBPersonaDueño()
        Dim contador As Integer
        Dim val As Integer = 0


        Dim i As Integer

        frmEditaDepartamento.cbPersonaD.Items.Clear()
        conectar()
        cmd.Connection = con
        cmd.CommandText = "SELECT count(Nombre) FROM tpersonas"
        cmd.CommandType = CommandType.Text
        dr = cmd.ExecuteReader
        If dr.Read Then
            contador = dr.Item(0)
        End If
        dr.Close()

        Dim VectorDepartamento(contador, 1) As String
        cmd.Connection = con
        cmd.CommandText = "SELECT ID, Nombre, ApPaterno, ApMaterno FROM  tpersonas ORDER BY Nombre"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString

                VectorDepartamento(i, 1) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString

                frmEditaDepartamento.cbPersonaD.Items.Add(VectorDepartamento(i, 1))
                i += 1

            End While
        End If
        dr.Close()
        desconectar()

        Return VectorDepartamento



    End Function



    Sub actualizarDepartamento(ByVal vID As Integer, ByVal vActual As String, ByVal vAnterior As String, ByVal vBlock As Integer, ByVal vCargo As Integer, ByVal vGrupo As Integer, ByVal vRol As String, ByVal vNic As String, ByVal vNicAgua As String, ByVal vDueño As Integer)

        conectar()

        cmd = New OdbcCommand("UPDATE tDepartamento set  NumeroActual='" + vActual + "',NumeroAnterior='" + vAnterior + "',IDBlock='" & vBlock & "',IDPersona='" & vCargo & "',IDGruposDeGastos='" & vGrupo & "', ROL ='" + vRol + "', NIC ='" + vNic + "',NICAgua='" + vNicAgua + "',IDPropietario = '" & vDueño & "' WHERE  ID ='" & vID & "'", con)

        If cmd.ExecuteNonQuery Then
            listaDepartamentos(vBlock)
            frmEditaDepartamento.Close()

        Else
            MessageBox.Show(msjNoActualizar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        desconectar()


    End Sub


End Module
